<?php

namespace app\admin\controller;

use Cassandra\Date;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Db;
use think\Validate;

class User extends Base
{
    public function index(){
        extract(input());
        $where = [];
        if (isset($username) && !empty($username)) {
            $where['username'] = ['like', '%' . $username . '%'];
        }
        $data = Db::name('tp_user')
            ->where($where)
            ->order('id Desc')
            ->paginate('3', false);
        foreach($data as &$v){
            $v['create_time'] = date('Y-m-d H:i:s', $v['create_time']);
            $v['last_login_time'] = date('Y-m-d H:i:s', $v['last_login_time']);
        }
        $page = $data->render();
        $this->assign('data',$data);
        $this->assign('page',$page);
        return $this->fetch('user');
    }
    public function add(){
        return $this->fetch();
    }
    public function addPost(){
        $data = input();
        $rule = [
            'username'  => 'require',
            'password' => 'require',
        ];
        $msg = [
            'username.require' => '用户名不能为空',
            'password.require' => '密码不能为空',
        ];
        $validate = new Validate($rule, $msg);
        $result = $validate->check($data);
        if (true !== $result) {
            return json(['code' => 2, 'msg' => $validate->getError()]);
        }
        $res = Db::name('tp_user')
            ->where('username',$data['username'])
            ->find();
        if ($res) {
            return json(['code' => 2, 'msg' => '用户已存在']);
        }
        if($data['password']!=''){
            $data['password']=md5($data['password']);
        }
        $data['create_time'] = time();
        $add = Db::name('tp_user')->insert($data);
        if ($add) {
            return json(['code' => 1, 'msg' => '添加成功']);
        } else {
            return json(['code' => 2, 'msg' => '添加失败']);
        }
    }
    public function edit(){
        $id = input('id');
        $edit = Db::name('tp_user')
            ->where('id',$id)
            ->find();
        $this->assign('data',$edit);
        return $this->fetch();
    }
    public function editPost(){
        $data = input();
        $rule = [
            'username'  => 'require',
        ];
        $msg = [
            'username.require' => '用户名不能为空',
        ];
        $validate = new Validate($rule, $msg);
        $result = $validate->check($data);
        if (true !== $result) {
            return json(['code' => 2, 'msg' => $validate->getError()]);
        }
        $edit = Db::name('tp_user')
            ->where('id',$data['id'])
            ->find();
        if(md5($data['old_password'])!=$edit['password']){
            return json(['code' => 2, 'msg' => '原密码错误']);
        }
        if ($data['password']!=''){
            $data['password']=md5($data['password']);
        }else{
            $data['password'] = $data['old_pass'];
        }
        unset($data['old_password']);
        unset($data['old_pass']);
        $editPost = Db::name('tp_user')
            ->where('id', $data['id'])
            ->update($data);
        if ($editPost) {
            return json(['code' => 1, 'msg' => '保存成功']);
        } else {
            return json(['code' => 2, 'msg' => '保存失败']);
        }
    }
    public function del(){
        $id = input('id');
        $del = Db::name('tp_user')->delete($id);
        if ($del) {
            return json(['code' => 1, 'msg' => '删除成功']);
        } else {
            return json(['code' => 2, 'msg' => '删除失败']);
        }
    }
    public function delete(){
        $ids = input('ids/a');
        $del = Db::name('tp_user')->delete($ids);
        if ($del) {
            return json(['code' => 1, 'msg' => '删除成功']);
        } else {
            return json(['code' => 2, 'msg' => '删除失败']);
        }
    }
    //导出
    public function exportExcel()
    {
        // 查询要导出的数据
        $data = Db::name('tp_user')->select();
        // 实例化
        $spreadsheet = new Spreadsheet();
        // 获取活动工作薄
        $sheet = $spreadsheet->getActiveSheet();

        // 获取单元格
        $cellA = $sheet->getCell('A1');
        // 设置单元格的值
        $cellA->setValue('ID');
        // 设置 A 列 列宽
        $sheet->getColumnDimension('A')->setWidth(10);
        // 设置第一行 行高
        $sheet->getRowDimension(1)->setRowHeight(20);

        $cellB = $sheet->getCell('B1');
        $cellB->setValue('用户名称');
        $sheet->getColumnDimension('B')->setWidth(20);

        $cellC = $sheet->getCell('C1');
        $cellC->setValue('密码');
        $sheet->getColumnDimension('C')->setWidth(40);

        $cellD = $sheet->getCell('D1');
        $cellD->setValue('最后登陆时间');
        $sheet->getColumnDimension('D')->setWidth(30);

        $cellE = $sheet->getCell('E1');
        $cellE->setValue('最后登陆IP');
        $sheet->getColumnDimension('E')->setWidth(20);

        $cellF = $sheet->getCell('F1');
        $cellF->setValue('创建时间');
        $sheet->getColumnDimension('F')->setWidth(30);

        // 设置样式 标题栏
        $styleArray = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '黑体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 设置样式 正文
        $styleArrayBody = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '宋体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 应用样式
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        $sheet->getStyle('B1')->applyFromArray($styleArray);
        $sheet->getStyle('C1')->applyFromArray($styleArray);
        $sheet->getStyle('D1')->applyFromArray($styleArray);
        $sheet->getStyle('E1')->applyFromArray($styleArray);
        $sheet->getStyle('F1')->applyFromArray($styleArray);


        // 从 A2 开始填充数据
        foreach ($data as $k => $v) {
            $n = $k + 2;
            // 获取单元格
            $cellA = $sheet->getCell('A'.$n);
            // 设置单元格的值
            $cellA->setValue($v['id']);

            $cellB = $sheet->getCell('B'.$n);
            $cellB->setValue($v['username']);

            $cellC = $sheet->getCell('C'.$n);
            $cellC->setValue($v['password']);

            $cellD = $sheet->getCell('D'.$n);
            $cellD->setValue(date("Y-m-d H-i-s",$v['last_login_time']));

            $cellE = $sheet->getCell('E'.$n);
            $cellE->setValue($v['last_login_ip']);

            $cellF = $sheet->getCell('F'.$n);
            $cellF->setValue(date("Y-m-d H-i-s",$v['create_time']));

            // 再给表格体设置样式
            $sheet->getStyle('A'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('B'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('C'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('D'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('E'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('F'.$n)->applyFromArray($styleArrayBody);

        }

        // 下载文件名
        $filename = '用户列表.xlsx';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$filename);
        header('Cache-Control: max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
    //导入
    public function Excel_export()
    {
        // 接收文件
        $file = $_FILES['file']['tmp_name'];
        // 创建读操作
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        // 打开文件、载入excel表格
        $spreadsheet = $reader->load($file);
        // 获取活动工作薄
        $sheet = $spreadsheet->getActiveSheet();


        // 获取总行数
        $highestRow = $sheet->getHighestRow();

        // 存放插入成功的记录
        $successLog = [];
        // 存放插入失败的记录
        $failLog = [];

        // 从第二行开始读取表格数据，循环写入数据库
        for ($i = 2; $i <= $highestRow; $i++) {
            // 第 1 次循环获取第 2列 第 2 行单元格的值，第 2 次循环获取第 3 列 第 3 行单元格的值
            $username = $sheet->getCellByColumnAndRow(2, $i)->getValue();
            $password = $sheet->getCellByColumnAndRow(3, $i)->getValue();
            $last_login_time = $sheet->getCellByColumnAndRow(4, $i)->getValue();
            $last_login_ip = $sheet->getCellByColumnAndRow(5, $i)->getValue();
            $create_time = $sheet->getCellByColumnAndRow(6, $i)->getValue();

            // 假设数据表有以下字段

            $data = [
                'username' => $username,
                'password' => $password,
                'last_login_time' => $last_login_time,
                'last_login_ip' => $last_login_ip,
                'create_time' => $create_time,

            ];
            $insert = Db::name('tp_user')->insert($data);
            if ($insert) {
                $successLog[] = '第' . $i . '条，插入成功';
            } else {
                $failLog[] = '第' . $i . '条，插入失败';
            }
        }
        // 将成功数和失败数返回给 ajax
        return json(['success' => count($successLog), 'fail' => count($failLog)]);
    }
}